/******************************************************************************
 * Product: Adempiere ERP & CRM Smart Business Solution                       *
 * Copyright (C) 1999-2006 ComPiere, Inc. All Rights Reserved.                *
 * This program is free software; you can redistribute it and/or modify it    *
 * under the terms version 2 of the GNU General Public License as published   *
 * by the Free Software Foundation. This program is distributed in the hope   *
 * that it will be useful, but WITHOUT ANY WARRANTY; without even the implied *
 * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.           *
 * See the GNU General Public License for more details.                       *
 * You should have received a copy of the GNU General Public License along    *
 * with this program; if not, write to the Free Software Foundation, Inc.,    *
 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.                     *
 * For the text or an alternative of this public license, you may reach us    *
 * ComPiere, Inc., 2620 Augustine Dr. #245, Santa Clara, CA 95054, USA        *
 * or via info@compiere.org or http://www.compiere.org/license.html           *
 *****************************************************************************/
package org.compiere.report;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.logging.Level;

import org.compiere.print.MPrintFormat;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Ini;
import org.compiere.util.Msg;

/**
 *  Statement of Account
 *
 *  @author Jorg Janke
 *  @version $Id: FinStatement.java,v 1.2 2006/07/30 00:51:05 jjanke Exp $
 *  @author Low Heng Sin
 *  - Remove update balance option to resolved Feature Request [ 1557707 ] and
 *    bug [1619917]
 *
 *  @author victor.perez@e-evolution.com, e-Evolution http://www.e-evolution.com
 * 			<li> FR [ 2520591 ] Support multiples calendar for Org 
 *			@see http://sourceforge.net/tracker2/?func=detail&atid=879335&aid=2520591&group_id=176962
 *	@author Armen Rizal, Goodwill Consulting
 *			<li>FR [2857076] User Element 1 and 2 completion - https://sourceforge.net/tracker/?func=detail&aid=2857076&group_id=176962&atid=879335
 *   
 */
public class InsStockLedger2 extends SvrProcess
{
	private Timestamp			p_DateAcct_From = null;
	private Timestamp			p_DateAcct_To = null;
	/**	Org Parameter					*/
	private int					p_AD_Org_ID = 0;
	/**	Product Parameter				*/
	private int					p_M_Product_ID = 0;
	/**	Parameter Where Clause			*/
	private StringBuffer		m_parameterWhere = new StringBuffer();
	/**	Start Time						*/
	private long 				m_start = System.currentTimeMillis();
	/**
	 *  Prepare - e.g., get Parameters.
	 */
	protected void prepare()
	{
		StringBuffer sb = new StringBuffer ("Record_ID=")
			.append(getRecord_ID());
		//	Parameter
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)
		{
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("DATEVALUE"))
			{
				p_DateAcct_From = (Timestamp)para[i].getParameter();
				p_DateAcct_To = (Timestamp)para[i].getParameter_To();
			}
			else if (name.equals("AD_Org_ID"))
				p_AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("M_Product_ID"))
				p_M_Product_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

		m_parameterWhere.append(" and fa.M_Product_ID = ").append( p_M_Product_ID)
			.append(" and fa.AD_ORG_ID = ").append(p_AD_Org_ID)
			.append(" and fa.AD_CLIENT_ID = ").append(getAD_Client_ID());

		log.fine(sb.toString());
	}	//	prepare

	/**************************************************************************
	 *  Perform process.
	 *  @return Message to be translated
	 */
	protected String doIt()
	{
		createBalanceLine();
		createDetailLines();

/*		int AD_PrintFormat_ID = 134;
		if (Ini.isClient())
			getProcessInfo().setTransientObject (MPrintFormat.get (getCtx(), AD_PrintFormat_ID, false));
		else
			getProcessInfo().setSerializableObject(MPrintFormat.get (getCtx(), AD_PrintFormat_ID, false));*/

		log.fine((System.currentTimeMillis() - m_start) + " ms");
		return "";
	}	//	doIt

	/**
	 * 	Create Beginning Balance Line
	 */
	private void createBalanceLine()
	{
		StringBuffer sb = new StringBuffer ("INSERT INTO T_STOCK_LEDGER "
			+ "(AD_PInstance_ID, Fact_Acct_ID, LevelNo,"
			+ "DATEVALUE, DESCRIPTION,"
			+ "COSTAMT, QTY, M_PRODUCT_ID, AD_CLIENT_ID, AD_ORG_ID, M_LOCATOR_ID, M_WAREHOUSE_ID, C_UOM_ID, COST, INQTY, OUTQTY) ");
		sb.append("SELECT ").append(getAD_PInstance_ID()).append(",0,0,")
			.append(DB.TO_DATE(p_DateAcct_From, true)).append(",")
			.append(DB.TO_STRING(Msg.getMsg(Env.getCtx(), "BeginningBalance"))).append(","
			+ "COALESCE(SUM(fa.AmtAcctDr-fa.AmtAcctCr),0), COALESCE(SUM(fa.Qty),0), fa.M_PRODUCT_ID, fa.AD_CLIENT_ID, " +
			"fa.AD_ORG_ID, fa.M_LOCATOR_ID, l.m_warehouse_id, p.c_uom_id, COALESCE(SUM(fa.AmtAcctDr-fa.AmtAcctCr),0)/COALESCE(SUM(fa.Qty),0), " +
			"CASE WHEN COALESCE(SUM(fa.Qty),0) >= 0 THEN COALESCE(SUM(fa.Qty),0) ELSE 0 END AS  inqty, " +
			"CASE WHEN COALESCE(SUM(fa.Qty),0) < 0 THEN abs(COALESCE(SUM(fa.Qty),0)) ELSE 0 END AS  outqty "
			+ "FROM Fact_Acct fa, M_Product_Acct pa, c_validcombination vc, m_product p, m_locator l "
			+ "WHERE fa.m_product_id = pa.m_product_id and fa.account_id = vc.account_id " +
			"and vc.c_validcombination_id = pa.p_asset_acct and p.m_product_id = fa.m_product_id and l.m_locator_id = fa.m_locator_id ").append(m_parameterWhere)
			.append(" AND TRUNC(fa.DateAcct, 'DD') < ").append(DB.TO_DATE(p_DateAcct_From))
			.append(" group by fa.M_PRODUCT_ID, fa.AD_CLIENT_ID, fa.AD_ORG_ID, fa.M_LOCATOR_ID, l.m_warehouse_id, p.c_uom_id ");
			
		int no = DB.executeUpdate(sb.toString(), get_TrxName());
		log.fine("#" + no + " (M_Product_ID=" + p_M_Product_ID + ")");
		log.finest(sb.toString());
	}	//	createBalanceLine

	/**
	 * 	Create Beginning Balance Line
	 */
	private void createDetailLines()
	{
		StringBuffer sb = new StringBuffer ("INSERT INTO T_STOCK_LEDGER "
			+ "(AD_PInstance_ID, Fact_Acct_ID, LevelNo,"
			+ "DATEVALUE, DESCRIPTION,"
			+ "COSTAMT, QTY, M_PRODUCT_ID, AD_CLIENT_ID, AD_ORG_ID, C_BPARTNER_ID, M_LOCATOR_ID, M_WAREHOUSE_ID, C_UOM_ID, COST, INQTY, OUTQTY) ");
		sb.append("SELECT ").append(getAD_PInstance_ID()).append(",fa.Fact_Acct_ID,1,")
			.append("TRUNC(fa.DateAcct, 'DD'),NULL,"
			+ "fa.AmtAcctDr-fa.AmtAcctCr, fa.Qty, fa.M_PRODUCT_ID, fa.AD_CLIENT_ID, fa.AD_ORG_ID, " +
			"fa.C_BPARTNER_ID, fa.M_LOCATOR_ID, l.m_warehouse_id, p.c_uom_id, (fa.AmtAcctDr-fa.AmtAcctCr)/fa.Qty, " +
			"CASE WHEN fa.Qty >= 0 THEN fa.Qty ELSE 0 END AS  inqty, " +
			"CASE WHEN fa.Qty < 0 THEN abs(fa.Qty) ELSE 0 END AS  outqty "
			+ "FROM Fact_Acct fa, M_Product_Acct pa, c_validcombination vc, m_product p, m_locator l "
			+ "WHERE fa.m_product_id = pa.m_product_id and fa.account_id = vc.account_id " +
			"and vc.c_validcombination_id = pa.p_asset_acct and p.m_product_id = fa.m_product_id and l.m_locator_id = fa.m_locator_id ").append(m_parameterWhere)
			.append(" AND TRUNC(fa.DateAcct, 'DD') BETWEEN ").append(DB.TO_DATE(p_DateAcct_From))
			.append(" AND ").append(DB.TO_DATE(p_DateAcct_To));
		//
		int no = DB.executeUpdate(sb.toString(), get_TrxName());
		log.fine("#" + no);
		log.finest(sb.toString());

		//	Set Name,Description
		String sql_select = "SELECT e.Name||'-'||fa.Description "
			+ "FROM Fact_Acct fa"
			+ " INNER JOIN AD_Table t ON (fa.AD_Table_ID=t.AD_Table_ID)"
			+ " INNER JOIN AD_Element e ON (t.TableName||'_ID'=e.ColumnName) "
			+ "WHERE r.Fact_Acct_ID=fa.Fact_Acct_ID";
		//	Translated Version ...
		sb = new StringBuffer ("UPDATE T_STOCK_LEDGER r SET (DESCRIPTION)=(")
			.append(sql_select).append(") "
			+ "WHERE Fact_Acct_ID <> 0 AND AD_PInstance_ID=").append(getAD_PInstance_ID());
		//
	   no = DB.executeUpdate(sb.toString(), get_TrxName());
	   log.fine("Name #" + no);
	   log.finest("Name - " + sb);

		//update for on hand qty
		
		StringBuffer pInstance = new StringBuffer ( 
			"select tsl.ad_pinstance_id, tsl.m_product_id, tsl.DESCRIPTION, tsl.datevalue, " +
			"tsl.c_bpartner_id, tsl.M_WAREHOUSE_ID, tsl.inqty, tsl.outqty, tsl.seq_no from t_stock_ledger tsl " +
			"where tsl.ad_pinstance_id = ").append(getAD_PInstance_ID()).append(" order by tsl.M_WAREHOUSE_ID, tsl.seq_no");	
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			try
			{
				pstmt = DB.prepareStatement(pInstance.toString(), get_TrxName());
				rs = pstmt.executeQuery();
				Double v_bal_qty = 0.00;
				int v_chk_warehouse_id = 0; 
				while (rs.next()) {
					int v_m_product_id = rs.getInt(2);
					String v_descr = rs.getString(3);
					int v_c_bpartner_id = rs.getInt(5);
					int v_warehouse_id = rs.getInt(6);
					Double v_inqty = rs.getDouble(7);
					Double v_outqty = rs.getDouble(8);
					int v_seq_no = rs.getInt(9);

					if (v_warehouse_id != v_chk_warehouse_id) {
						
						v_chk_warehouse_id = v_warehouse_id;
						v_bal_qty = 0.00;
					}					
					//v_bal_qty = v_bal_qty + v_qtyonhand + v_inqty - v_outqty;
					v_bal_qty = v_bal_qty + v_inqty - v_outqty;
					
					//if (v_doc_no != null)
					//{
/*						sb = new StringBuffer ("UPDATE t_stock_ledger tsl SET tsl.qtyonhand = ")
						.append(v_bal_qty).append(" WHERE AD_PInstance_ID=").append(getAD_PInstance_ID())
						.append(" and tsl.m_product_id = ").append(v_m_product_id).append(" and tsl.DESCRIPTION = '").append(v_descr)
						.append("' and COALESCE(tsl.c_bpartner_id, 0) = ").append(v_c_bpartner_id);
*/
						sb = new StringBuffer ("UPDATE t_stock_ledger tsl SET tsl.qtyonhand = ")
						.append(v_bal_qty).append(" WHERE AD_PInstance_ID=").append(getAD_PInstance_ID())
						.append(" and tsl.seq_no = ").append(v_seq_no);
						
						no = DB.executeUpdate(sb.toString(), get_TrxName());
						log.fine("Name #" + no);
						log.finest("Name - " + sb);
					//} 

				}
			}catch (Exception e) 
			{
				log.log(Level.SEVERE, pInstance.toString(), e);
			}finally {
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	   
	   
	}	//	createDetailLines

}	//	FinStatement
